<?php

require_once('functions/pageLoad.php');
// loginRequired();

$user_session = explode("_", $_SESSION['user']);
$user_id = $user_session[0];

$sql = "SELECT id FROM tradesman WHERE user_id = '".$user_id."' AND active = 1 AND (subscription_expiration > NOW() OR lucky = 1)";
$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
$approved_tradesmen = mysql_num_rows($query);

// if($approved_tradesmen == '1'){

$content = '<table width="100%" class="standard">';

$trade_sql = (isset($_GET['trade']) && strlen($_GET['trade']) > 0) ? " AND trades.url = '".mysql_real_escape_string($_GET['trade'])."'" : '' ;
$area_sql = (isset($_GET['area']) && strlen($_GET['area']) > 0) ? " AND areas.url = '".mysql_real_escape_string($_GET['area'])."'" : '' ;

if(isset($_GET['postcode']) && strlen($_GET['postcode']) > 0 && isset($_GET['radius']) && strlen($_GET['radius']) > 0){

	$address = urldecode($_GET['postcode'])." UK";
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, "http://maps.google.com/maps/geo?q=".urlencode($address)."&output=csv&oe=utf8&sensor=false&key=".$settings['google_api_key']);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        $output = curl_exec($ch);
        curl_close($ch);
	$outputArr = explode(",",$output);

if(strlen($outputArr[2]) > 0 && strlen($outputArr[3]) > 0){

$distance_sql_1 = ", ROUND(SQRT(POW((69.1 * (".$outputArr[2]." - latitude)), 2) + POW((53 * (".$outputArr[3]." - longitude)), 2)), 1) AS distance";
$distance_sql_2 = " HAVING distance <= ".mysql_real_escape_string($_GET['radius']);

}

}

/*
$sql = "SELECT jobs.id, title, area, trade, date_added, COUNT(quotes.id) AS quotes".$distance_sql_1." FROM jobs 
	LEFT JOIN trades ON jobs.trade_id = trades.id 
	LEFT JOIN areas ON jobs.area_id = areas.id 
	LEFT JOIN quotes ON jobs.id = quotes.job_id 
	WHERE jobs.status = 'Open' AND jobs.custom != '1' AND jobs.job_active = '1'".$trade_sql.$area_sql." GROUP BY jobs.id".$distance_sql_2." ORDER BY date_added DESC";
	
*/

$sql = "SELECT jobs.id, title, area, trade, date_added, COUNT(quotes.id) AS quotes, jobs.status".$distance_sql_1." FROM jobs 
	LEFT JOIN trades ON jobs.trade_id = trades.id 
	LEFT JOIN areas ON jobs.area_id = areas.id 
	LEFT JOIN quotes ON jobs.id = quotes.job_id 
	WHERE jobs.custom != '1' AND jobs.job_active = '1' AND jobs.deleted != 1".$trade_sql.$area_sql." GROUP BY jobs.id".$distance_sql_2." ORDER BY date_added DESC";

$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
$real_rows = mysql_num_rows($query);

if($real_rows > 0){

$limit = ($_GET['page'] < 2) ? '0,20' : (($_GET['page']-1)*20).',20';

$sql.= " LIMIT ".$limit;
$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

$i = 1;

while($jobs_rs=mysql_fetch_assoc($query)){

$job_link = build_job_link($jobs_rs['trade'],$jobs_rs['area'],$jobs_rs['id']);
$highlight = (is_int($i / 2)) ? ' class="highlight"' : '';
$jobs_rs['quotes'] = ($jobs_rs['quotes'] == '0') ? 'Job '.$jobs_rs['status'] : $jobs_rs['quotes'].' Quotes' ;
$content.='<tr'.$highlight.'><td class="alignLeft"><a href="'.$job_link.'">'.ucfirst($jobs_rs['title']).'</a><br /><strong>'.$jobs_rs['area'].'</strong></td><td>'.date("d/m/Y",strtotime($jobs_rs['date_added'])).'</td><td>'.$jobs_rs['quotes'].'</td></tr>';

$i++;

}

$content.='</table><div id="page_selector">'.pageSelector($real_rows, $_GET['page'], 20).'</div>';

}

else{

$content = '<p>There are currently no jobs matching that criteria.</p>';

}


// search area

// trades dropdown

$trades_dropdown = '<option value="">All trades</option>';

$sql = "SELECT trade, url FROM trades ORDER BY trade";
$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
while($trades_rs=mysql_fetch_assoc($query)){
$active = (isset($_GET['trade']) && $_GET['trade'] == $trades_rs['url']) ? ' selected="selected"' : '' ;
$real_trade = (isset($_GET['trade']) && $_GET['trade'] == $trades_rs['url']) ? htmlentities($trades_rs['trade']) : $real_trade;
$trades_dropdown.='<option value="'.$trades_rs['url'].'"'.$active.'>'.htmlentities($trades_rs['trade']).'</option>'."\r\n";
}

// areas dropdown

$areas_dropdown = '<option value="">All areas</option>';

$sql = "SELECT area, url FROM areas ORDER BY area";
$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
while($areas_rs=mysql_fetch_assoc($query)){
$active = (isset($_GET['area']) && $_GET['area'] == $areas_rs['url']) ? ' selected="selected"' : '' ;
$real_area = (isset($_GET['area']) && $_GET['area'] == $areas_rs['url']) ? htmlentities($areas_rs['area']) : $real_area;
$areas_dropdown.='<option value="'.$areas_rs['url'].'"'.$active.'>'.htmlentities($areas_rs['area']).'</option>'."\r\n";
}

// radius dropdown

$i = 10;

while($i <= 100){
$active = ($i == $_GET['radius']) ? ' selected="selected"' : '' ;
$radius_dropdown.='<option value="'.$i.'"'.$active.'>'.$i.' miles</option>';
$i = $i + 10;
}

$rightColumn_content='

<aside id="search_panel">

<h1>Search jobs</h1>

<form method="get" action="/view-jobs">

<!--<div class="formRowWrapper">
<div class="formRowLeft" style="width: 60px;"><label for="area">Area:</label></div>
<div class="formRowRight" style="width: 163px;"><select name="area" id="area" style="width: 153px;">
'.$areas_dropdown.'
</select></div>
</div> -->

<div class="rowWrapper">
<div class="rowLeft"><label for="postcode">Postcode:</label></div>
<div class="rowRight"><input type="text" name="postcode" id="postcode" value="'.urldecode($_GET['postcode']).'" style="width: 60px;" class="textbox" /></div>
</div>

<div class="rowWrapper">
<div class="rowLeft"><label for="radius">Radius:</label></div>
<div class="rowRight"><select name="radius" id="radius">
'.$radius_dropdown.'
</select></div>
</div>

<div class="rowWrapper">
<div class="rowLeft"><label for="trade">Trade:</label></div>
<div class="rowRight"><select name="trade" id="trade">
'.$trades_dropdown.'
</select></div>
</div>

<script type="text/javascript">document.write(\'<\'+\'a onclick="return jobs_search();" style="float: right; cursor: pointer; margin-top: 5px;"><\'+\'img src="/images/search_button.gif" alt="Search"><\'+\'/a>\');</script>
<noscript><input type="image" src="/images/search_button.gif" alt="search" style="float: right; cursor: pointer; margin-top: 5px;"></noscript>

</form>
</aside>
';

// page header & title

$big_page_header = $page_title = (strlen($_GET['trade']) > 0 && strlen($_GET['area']) > 0) ?  $real_trade.' jobs in '.$real_area : $big_page_header ;
$big_page_header = $page_title = (strlen($_GET['trade']) == 0 && strlen($_GET['area']) > 0) ?  'Jobs in '.$real_area : $big_page_header ;
$big_page_header = $page_title = (strlen($_GET['trade']) > 0 && strlen($_GET['area']) == 0) ?  $real_trade.' jobs' : $big_page_header ;
$big_page_header = $page_title = (strlen($_GET['trade']) == 0 && strlen($_GET['area']) == 0) ?  'Jobs' : $big_page_header ;

/*

}

else{

$big_page_header = $page_title = 'Tradesmen only';

$content = '<div class="yellow_box"><p>You must have an active subscription and trade account to view this area.</p></div>';

}

*/

include('includes/meta.php');
include('includes/header.php');
include('includes/navigation.php');

?>

<h1><?php echo $big_page_header; ?></h1>

<div class="yellow_box">
<p><strong>Are you a tradesman? Want to join our team?</strong> <a href="/register-my-business"><strong>Register your business for FREE</strong></a> and receive job requests for your companies trades in your area.</p>
</div>

<?php echo $content; ?>

<?php 

include('includes/rightColumn.php');
include('includes/footer.php');

?>